﻿using KPIS.GERP.GBMS.APP_UI.Common;
using KPIS.GERP.GBMS.BLL;
using KPIS.GERP.GBMS.IBLL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GFMS.APP.App_UI.Commons;
using System;
using System.Collections.Generic;
using System.Data;
using Telerik.Web.UI;
using System.Linq;
using KPIS.GERP.GBMS.Reports;
using CrystalDecisions.Shared;
using KPIS.DBM;

namespace KPIS.GERP.GBMS.APP_UI.Form
{
    public partial class BGM_Project_Payment_Report_Old : System.Web.UI.Page
    {
        private Main main = new Main();
        private IBGMProjectPaymentReportBLL PaymentReport = new BGMProjectPaymentReportBLL();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                txtBgmYear2.Text = SystemInfo._CurrentYearReq.ToString();
                //ddlMonth.SelectedValue = DateTime.Today.Month.ToString();
                //LoadActStratList();
                //LoadActSubStratList();

                //LoadActStratList2();
                //LoadActSubStratList2();
                //LoadProject();
            }
        }

        #region report 1

        //protected void btnPrintReport_Click(object sender, EventArgs e)
        //{
        //    MailMerge mailMerge = new MailMerge();
        //    Dictionary<string, DataTable> dtbs = new Dictionary<string, DataTable>();
        //    Dictionary<string, string> dic = new Dictionary<string, string>();

        //    DateTime? StartDate = txtStartDate.GetDateValue();
        //    DateTime? EndDate = txtEndDate.GetDateValue();
        //    string strActStratList = ddlActStratList.SelectedValue;
        //    string strActSubStratList = ddlActSubStratList.SelectedValue;
        //    string year = txtBgmYear.Text;

        //    DataTable dtb = PaymentReport.SearchReport(year, StartDate, EndDate, strActStratList, strActSubStratList);

        //    int nRow = 3;
        //    double SumAmount = 0;

        //    for (int i = 0; i < dtb.Rows.Count; i++)
        //    {
        //        DataRow dr = dtb.Rows[i];

        //        string strActSeq = dr["act_seq"].ToString();
        //        string strSubSeq = dr["sub_seq"].ToString();
        //        string strProjSeq = dr["bgm_proj_seq"].ToString();
        //        string strExpSeq = dr["bgm_exp_seq"].ToString();
        //        string strActName = dr["act_name"].ToString();
        //        string strSubName = dr["sub_name"].ToString();
        //        string strProjName = dr["bgm_proj_name"].ToString();
        //        string strExpName = dr["bgm_exp_name"].ToString();
        //        string strAmount = dr["amount"].ToString();

        //        if (i == 0)
        //        {
        //            dic.Add("A3", strActName);
        //            dic.Add("B4", strSubName);
        //            dic.Add("C5", strProjName);
        //            dic.Add("D6", "ค่าใช้จ่าย");
        //            dic.Add("E6", "จำนวนเงิน");
        //            nRow = 7;
        //        }

        //        dic.Add("D" + nRow.ToString(), strExpName);
        //        dic.Add("E" + nRow.ToString(), strAmount);
        //        SumAmount = SumAmount + (main.ConvertNullDouble(strAmount) ?? 0);
        //        nRow++;

        //        if (i == dtb.Rows.Count - 1 || strProjSeq != dtb.Rows[i + 1]["bgm_proj_seq"].ToString())
        //        {
        //            dic.Add("D" + nRow.ToString(), "รวม");
        //            dic.Add("E" + nRow.ToString(), SumAmount.ToString("#,##0.00"));
        //            nRow++;

        //            if (i < dtb.Rows.Count - 1)
        //            {
        //                if (strSubSeq != dtb.Rows[i + 1]["sub_seq"].ToString())
        //                {
        //                    if (strActSeq != dtb.Rows[i + 1]["act_seq"].ToString())
        //                    {
        //                        dic.Add("A" + nRow.ToString(), dtb.Rows[i + 1]["act_name"].ToString());
        //                        nRow++;
        //                    }

        //                    dic.Add("B" + nRow.ToString(), dtb.Rows[i + 1]["sub_name"].ToString());
        //                    nRow++;
        //                }

        //                dic.Add("C" + nRow.ToString(), dtb.Rows[i + 1]["bgm_proj_name"].ToString());
        //                nRow++;
        //                dic.Add("D" + nRow.ToString(), "ค่าใช้จ่าย");
        //                dic.Add("E" + nRow.ToString(), "จำนวนเงิน");
        //                nRow++;
        //            }
        //        }
        //    }

        //    mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/แสดงรายจ่ายโครงการ.xlsx"), string.Format("แสดงรายจ่ายโครงการ{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), dtbs, dic);

        //}

        //public void LoadActStratList()
        //{
        //    string year = txtBgmYear.Text;

        //    ddlActStratList.DataSource = PaymentReport.LoadActStratList(year);
        //    ddlActStratList.DataBind();
        //    ddlActStratList.Items.Insert(0, new RadComboBoxItem("ทั้งหมด", ""));
        //    ddlActStratList.SelectedIndex = 0;
        //}

        //public void LoadActSubStratList()
        //{
        //    if (ddlActStratList.SelectedValue.Length > 0)
        //    {
        //        string year = txtBgmYear.Text;

        //        ddlActSubStratList.DataSource = PaymentReport.LoadActSubStratList(year, ddlActStratList.SelectedValue);
        //        ddlActSubStratList.DataBind();
        //        ddlActSubStratList.Items.Insert(0, new RadComboBoxItem("ทั้งหมด", ""));
        //        ddlActSubStratList.SelectedIndex = 0;
        //    }
        //    else
        //    {
        //        ddlActSubStratList.Items.Clear();
        //        ddlActSubStratList.Items.Insert(0, new RadComboBoxItem("ทั้งหมด", ""));
        //        ddlActSubStratList.SelectedIndex = 0;
        //    }
        //}

        //protected void ddlActStratList_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        //{
        //    LoadActSubStratList();
        //}

        //protected void txtBgmYear_TextChanged(object sender, EventArgs e)
        //{
        //    LoadActSubStratList();
        //    LoadActStratList();
        //}

        #endregion

        #region report 2

        protected void txtBgmYear2_TextChanged(object sender, EventArgs e)
        {
            //LoadActSubStratList2();
            //LoadActStratList2();
        }

        //private void LoadActStratList2()
        //{
        //    string year = txtBgmYear2.Text;

        //    ddlActStratList2.DataSource = PaymentReport.LoadActStratList(year);
        //    ddlActStratList2.DataBind();
        //    ddlActStratList2.Items.Insert(0, new RadComboBoxItem("ทั้งหมด", ""));
        //    ddlActStratList2.SelectedIndex = 0;
        //}

        //private void LoadActSubStratList2()
        //{
        //    ddlProject.Items.Clear();
        //    if (ddlActStratList2.SelectedValue.Length > 0)
        //    {
        //        string year = txtBgmYear.Text;

        //        ddlActSubStratList2.DataSource = PaymentReport.LoadActSubStratList(year, ddlActStratList2.SelectedValue);
        //        ddlActSubStratList2.DataBind();
        //    }
        //    else
        //    {
        //        ddlActSubStratList2.Items.Clear();
        //    }
        //    ddlActSubStratList2.Items.Insert(0, new RadComboBoxItem("ทั้งหมด", ""));
        //    ddlActSubStratList2.SelectedIndex = 0;
        //}

        //private void LoadProject()
        //{
        //    ddlProject.Items.Clear();
        //    if (ddlActSubStratList2.SelectedValue.Length > 0)
        //    {
        //        ddlProject.DataSource = PaymentReport.LoadProject(null, ddlActSubStratList2.SelectedValue);
        //        ddlProject.DataBind();
        //    }
        //    else
        //    {
        //        ddlProject.Items.Clear();
        //    }
        //    ddlProject.Items.Insert(0, new RadComboBoxItem("ทั้งหมด", ""));
        //    ddlProject.SelectedIndex = 0;
        //}

        //protected void ddlActStratList2_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        //{
        //    LoadActSubStratList2();
        //}

        //protected void ddlActSubStratList2_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        //{
        //    LoadProject();
        //}

        //private string Report2Type
        //{
        //    get
        //    {
        //        string report2Type = "";
        //        if (RadioButton1.Checked) report2Type = "Strat";
        //        else if (RadioButton2.Checked) report2Type = "SubStrat";
        //        else if (RadioButton3.Checked) report2Type = "Proj";
        //        return report2Type;
        //    }
        //}

        protected void RadioButton3_CheckedChanged(object sender, EventArgs e)
        {
            //subStratLbl.Visible = ddlActSubStratList2.Visible = true;
            //projLbl.Visible = ddlProject.Visible = true;
        }

        protected void RadioButton2_CheckedChanged(object sender, EventArgs e)
        {
            //subStratLbl.Visible = ddlActSubStratList2.Visible = true;
            //projLbl.Visible = ddlProject.Visible = false;
        }

        protected void RadioButton1_CheckedChanged(object sender, EventArgs e)
        {
            //subStratLbl.Visible = ddlActSubStratList2.Visible = false;
            //projLbl.Visible = ddlProject.Visible = false;
        }

        private DataTable GetData()
        {
            DateTime? dtStartDate = txtStartDate.GetDateValue();
            DateTime? dtEndDate = txtEndDate.GetDateValue();
            int? nBgmYear = main.ConvertNullInt(txtBgmYear2.Text);

            if (!dtStartDate.HasValue || !dtEndDate.HasValue || !nBgmYear.HasValue)
            {
                main.MessageAlert(Page, "กรอกข้อมูลให้ครบถ้วน");
                return null;
            }

            DBManager db = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            db.Open();
            db.CreateParameters(3);
            db.AddParameters(0, "@bgm_year", nBgmYear.Value);
            db.AddParameters(1, "@start_date", dtStartDate.Value);
            db.AddParameters(2, "@end_date", dtEndDate.Value);

            string strSQL = @"
select pj.bgm_proj_code as project_code
, pj.bgm_proj_name as project_name
, sub.bgm_strat_list_name as sub_act_name
, act.bgm_strat_list_name as act_name
, coalesce(pje.amount1, 0) + coalesce(rpt1.amount1, 0) as amount11
, coalesce(pje.amount2, 0) + coalesce(rpt1.amount2, 0) as amount12
, coalesce(pje.amount1, 0) + coalesce(rpt1.amount1, 0) + coalesce(pje.amount2, 0) + coalesce(rpt1.amount2, 0) as amount1
, coalesce(po.amount1, 0) - coalesce(mrpo.amount1, 0) as amount21
, coalesce(po.amount2, 0) + coalesce(mrpo.amount2, 0) as amount22
, coalesce(po.amount1, 0) + coalesce(mrpo.amount1, 0) + coalesce(po.amount2, 0) + coalesce(mrpo.amount2, 0) as amount2
, coalesce(mr.amount1, 0) - coalesce(p.amount1, 0) as amount31
, coalesce(mr.amount2, 0) - coalesce(p.amount2, 0) as amount32
, coalesce(mr.amount1, 0) - coalesce(p.amount1, 0) + coalesce(mr.amount2, 0) - coalesce(p.amount2, 0) as amount3
, coalesce(p.amount1, 0) - coalesce(r.amount1, 0) as amount41
, coalesce(p.amount2, 0) - coalesce(r.amount2, 0) as amount42
, coalesce(p.amount1, 0) - coalesce(r.amount1, 0) + coalesce(p.amount2, 0) - coalesce(r.amount2, 0) as amount4
, coalesce(pje.amount1, 0) + coalesce(rpt1.amount1, 0) - (coalesce(po.amount1, 0) - coalesce(mrpo.amount1, 0)) - (coalesce(mr.amount1, 0) - coalesce(p.amount1, 0)) - (coalesce(p.amount1, 0) - coalesce(r.amount1, 0)) as remain_amount1
, coalesce(pje.amount2, 0) + coalesce(rpt1.amount2, 0) - (coalesce(po.amount2, 0) - coalesce(mrpo.amount2, 0)) - (coalesce(mr.amount2, 0) - coalesce(p.amount2, 0)) - (coalesce(p.amount2, 0) - coalesce(r.amount2, 0)) as remain_amount2
, coalesce(pje.amount1, 0) + coalesce(rpt1.amount1, 0) - (coalesce(po.amount1, 0) - coalesce(mrpo.amount1, 0)) - (coalesce(mr.amount1, 0) - coalesce(p.amount1, 0)) - (coalesce(p.amount1, 0) - coalesce(r.amount1, 0))
+ coalesce(pje.amount2, 0) + coalesce(rpt1.amount2, 0) - (coalesce(po.amount2, 0) - coalesce(mrpo.amount2, 0)) - (coalesce(mr.amount2, 0) - coalesce(p.amount2, 0)) - (coalesce(p.amount2, 0) - coalesce(r.amount2, 0)) as remain_amount
, '' as print_date

from bgm_project pj
left join bgm_strategy_data_mapping map1 on pj.bgm_strat_list_seq = map1.bgm_strat_list_seq
	and map1.bgm_year = pj.bgm_year
left join bgm_strategy_data_mapping map2 on map1.upper_bgm_strat_data_map_seq = map2.bgm_strat_data_map_seq
left join bgm_strategy_data_list sub on map1.bgm_strat_list_seq = sub.bgm_strat_list_seq
left join bgm_strategy_data_list act on map2.bgm_strat_list_seq = act.bgm_strat_list_seq

left join (select pje.bgm_proj_seq
	, sum(case when pje.bgm_source_seq = 1 then coalesce(pje.total_amount, 0) else 0 end) as amount1
	, sum(case when pje.bgm_source_seq = 11 then coalesce(pje.total_amount, 0) else 0 end) as amount2
	from bgm_project_expense pje
	group by pje.bgm_proj_seq
) pje on pj.bgm_proj_seq = pje.bgm_proj_seq

left join (select pje.bgm_proj_seq
	, sum(case when pje.bgm_source_seq = 1 then coalesce(rpt.cal_amount, 0) else 0 end) as amount1
	, sum(case when pje.bgm_source_seq = 11 then coalesce(rpt.cal_amount, 0) else 0 end) as amount2
	from bgm_project_report rpt
	left join bgm_project_expense pje on rpt.bgm_proj_exp_seq = pje.bgm_proj_exp_seq
	where rpt.record_status <> 'D'
	and rpt.rpt_type in (2,3)
	group by pje.bgm_proj_seq
) rpt1 on pj.bgm_proj_seq = rpt1.bgm_proj_seq

left join (select pje.bgm_proj_seq
	, sum(case when pje.bgm_source_seq = 1 then (COALESCE(quo.NetAmount, 0) / COALESCE(quo.Qty, 1)) * ppri.Qty else 0 end) as amount1
	, sum(case when pje.bgm_source_seq = 11 then (COALESCE(quo.NetAmount, 0) / COALESCE(quo.Qty, 1)) * ppri.Qty else 0 end) as amount2

	from IcmPurchaseRequestItem pri
  inner join IcmPurchaseRequest pr on pri.PrId = pr.PrId
	inner join IcmBidderQuotation quo on pri.PrItemId = quo.PrItemId
	inner join IcmPurchaseOrder po on quo.BidderId = po.BidderId
	inner join (select ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId, sum(ppri.Qty) as Qty
		from IcmPrePurchaseRequestItem ppri
		group by ppri.PrItemId, ppri.BgSourceId, ppri.ProjOperExpId
	) ppri on pri.PrItemId = ppri.PrItemId
	inner join bgm_project_operation_expense ope on ppri.ProjOperExpId = ope.bgm_proj_oper_exp_seq
	inner join bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq
	where po.RecordStatus = 'A'
	and pr.PurchaseDate >= @start_date
	and pr.PurchaseDate <= @end_date
	group by pje.bgm_proj_seq
) po on pj.bgm_proj_seq = po.bgm_proj_seq

left join (select op.bgm_proj_seq
	, sum(case when mr.bgm_source_seq = 1 then coalesce(mrd.amount, 0) else 0 end) as amount1
	, sum(case when mr.bgm_source_seq = 11 then coalesce(mrd.amount, 0) else 0 end) as amount2
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	where mr.record_status = 'A'
	and mr.money_req_date >= @start_date
	and mr.money_req_date <= @end_date
	and mr.event_seq = 11
	group by op.bgm_proj_seq
) mrpo on pj.bgm_proj_seq = mrpo.bgm_proj_seq

left join (select op.bgm_proj_seq
	, sum(case when mr.bgm_source_seq = 1 then coalesce(mrd.amount, 0) else 0 end) as amount1
	, sum(case when mr.bgm_source_seq = 11 then coalesce(mrd.amount, 0) else 0 end) as amount2
	from fim_money_request_dtls mrd
	inner join fim_money_requests mr on mrd.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	where mr.record_status = 'A'
	and mr.money_req_date >= @start_date
	and mr.money_req_date <= @end_date
	group by op.bgm_proj_seq
) mr on pj.bgm_proj_seq = mr.bgm_proj_seq

left join (select op.bgm_proj_seq
	, sum(case when p.bgm_source_seq = 1 then coalesce(p.amount, 0) else 0 end) as amount1
	, sum(case when p.bgm_source_seq = 11 then coalesce(p.amount, 0) else 0 end) as amount2
	from fim_payments p
	inner join fim_money_requests mr on p.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	where p.record_status = 'A'
	and p.pay_date >= @start_date
	and p.pay_date <= @end_date
	group by op.bgm_proj_seq
) p on pj.bgm_proj_seq = p.bgm_proj_seq

left join (select op.bgm_proj_seq
	, sum(case when r.bgm_source_seq = 1 then coalesce(r.amount, 0) else 0 end) as amount1
	, sum(case when r.bgm_source_seq = 11 then coalesce(r.amount, 0) else 0 end) as amount2
	from fim_receives r
	inner join fim_money_requests mr on r.money_req_seq = mr.money_req_seq
	inner join bgm_project_operation op on mr.bgm_proj_oper_seq = op.bgm_proj_oper_seq
	where r.record_status not in ('D', 'C')
	and r.receive_date >= @start_date
	and r.receive_date <= @end_date
	group by op.bgm_proj_seq
) r on pj.bgm_proj_seq = r.bgm_proj_seq

where pj.record_status = 'A'
and pj.bgm_year = @bgm_year"
                + (rblProjectType.SelectedValue == "1" ? " and pj.prj_type_seq not in (5, 6)" : rblProjectType.SelectedValue == "2" ? " and pj.prj_type_seq in (5, 6)" : "");

            DataTable dtb = db.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            db.Dispose();

            foreach (DataRow dr in dtb.Rows)
            {
                dr["print_date"] = dtStartDate.Value.ToLongDateString() + " ถึง " + dtEndDate.Value.ToLongDateString();
            }

            return dtb;
        }

        protected void btnPrintReport2_Click(object sender, EventArgs e)
        {
            var dtb = GetData();
            
            if (rblArrange.SelectedValue == "1")
            {
                ProjectReportAct rpt = new ProjectReportAct();

                rpt.SetDataSource(dtb);// Provide Dataset for report : Ds is DataSet
                rpt.ExportToHttpResponse(ExportFormatType.Excel, Response, true, "ProjectReportAct");
            }
            else if (rblArrange.SelectedValue == "2")
            {
                ProjectReportSub rpt = new ProjectReportSub();

                rpt.SetDataSource(dtb);// Provide Dataset for report : Ds is DataSet
                rpt.ExportToHttpResponse(ExportFormatType.Excel, Response, true, "ProjectReportSub");
            }
            else if (rblArrange.SelectedValue == "3")
            {
                ProjectReport rpt = new ProjectReport();

                rpt.SetDataSource(dtb);// Provide Dataset for report : Ds is DataSet
                rpt.ExportToHttpResponse(ExportFormatType.Excel, Response, true, "ProjectReport");
            }
        }

        protected void btnPrintReport3_Click(object sender, EventArgs e)
        {
            var dtb = GetData();
            
            if (rblArrange.SelectedValue == "1")
            {
                ProjectReportAct rpt = new ProjectReportAct();

                rpt.SetDataSource(dtb);// Provide Dataset for report : Ds is DataSet
                rpt.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, "ProjectReportAct");
            }
            else if (rblArrange.SelectedValue == "2")
            {
                ProjectReportSub rpt = new ProjectReportSub();

                rpt.SetDataSource(dtb);// Provide Dataset for report : Ds is DataSet
                rpt.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, "ProjectReportSub");
            }
            else if (rblArrange.SelectedValue == "3")
            {
                ProjectReport rpt = new ProjectReport();

                rpt.SetDataSource(dtb);// Provide Dataset for report : Ds is DataSet
                rpt.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, "ProjectReport");
            }
        }

        //protected void btnPrintReport2_Click_old(object sender, EventArgs e)
        //{
        //    MailMerge mailMerge = new MailMerge();
        //    Dictionary<string, DataTable> dtbs = new Dictionary<string, DataTable>();
        //    Dictionary<string, object> dic = new Dictionary<string, object>();

        //    string strBgmYear = txtBgmYear.Text;
        //    string strStratSeq = ddlActStratList2.SelectedValue;
        //    string strStratName = ddlActStratList2.Text;
        //    string strSubStratSeq = ddlActSubStratList2.SelectedValue;
        //    string strSubStratName = ddlActSubStratList2.Text;
        //    string strProjSeq = ddlProject.SelectedValue;
        //    string strProjName = ddlProject.Text;

        //    DataTable dtb = PaymentReport.SearchReport2(strBgmYear, strStratSeq, strSubStratSeq, ddlProject.SelectedValue, Report2Type);

        //    {
        //        // insert head criteria
        //        dic.Add("AK3", RadioButton1.Checked ? RadioButton1.Text : (RadioButton2.Checked ? RadioButton2.Text : RadioButton3.Text));
        //        dic.Add("AK4", strBgmYear);
        //        dic.Add("AK5", strStratName);
        //        dic.Add("AK6", strSubStratName);
        //        dic.Add("AK7", strProjName);
        //    }

        //    int nRow = 4;
        //    double RecordAmount = 0;
        //    List<Record> PlotedRecord = new List<Record>();
        //    for (int i = 0; i < dtb.Rows.Count; i++)
        //    {
        //        DataRow dr = dtb.Rows[i];
        //        int row;

        //        int uniqueId = 0;

        //        int bgmProjSeq = 0;
        //        string bgmProjCode = string.Empty;
        //        string bgmProjName = string.Empty;

        //        int sbStSeq = 0;
        //        string sbStName = string.Empty;

        //        switch (Report2Type)
        //        {
        //            case "Proj":
        //                uniqueId = Convert.ToInt32(dr["bgm_proj_seq"]);
        //                bgmProjSeq = Convert.ToInt32(dr["bgm_proj_seq"]);
        //                bgmProjCode = dr["bgm_proj_code"] as string;
        //                bgmProjName = dr["bgm_proj_name"] as string;
        //                sbStSeq = dr["sbst_seq"] as int? ?? 0;
        //                sbStName = dr["sbst_name"] as string;
        //                break;
        //            case "SubStrat":
        //                uniqueId = Convert.ToInt32(dr["sbst_seq"]);
        //                sbStSeq = dr["sbst_seq"] as int? ?? 0;
        //                sbStName = dr["sbst_name"] as string;
        //                break;
        //            case "Strat":
        //                uniqueId = Convert.ToInt32(dr["st_seq"]);
        //                break;
        //            default:
        //                throw new Exception("ประเภทรายงานไม่ถูกต้อง");
        //        }

        //        int monthCreate = Convert.ToInt32(dr["month_create"]);
        //        decimal realPaymentBudget = dr["real_payment_budget"] as decimal? ?? 0;
        //        decimal realPaymentOutBudget = dr["real_payment_out_budget"] as decimal? ?? 0;
        //        if (PlotedRecord.All(o => o.UniqueId != uniqueId))
        //        {
        //            row = nRow++;
        //            var record = new Record
        //                {
        //                    RowIndex = row
        //                };
        //            record.UniqueId = uniqueId;
        //            record.AddOrUpdateMonthPayment(monthCreate, realPaymentBudget, realPaymentOutBudget);
        //            record.StSeq = dr["st_seq"] as int? ?? 0;
        //            record.StName = dr["st_name"] as string;
        //            record.SbStSeq = sbStSeq;
        //            record.SbStName = sbStName;
        //            record.BgmProjSeq = bgmProjSeq;
        //            record.BgmProjCode = bgmProjCode;
        //            record.BgmProjName = bgmProjName;
        //            record.AvailBudgetAmount = dr["bath_amount_budget"] as decimal? ?? 0;
        //            record.AvailOutBudgetAmount = dr["bath_amount_out_budget"] as decimal? ?? 0;
        //            PlotedRecord.Add(record);
        //        }
        //        else
        //        {
        //            PlotedRecord.First(o => o.UniqueId == uniqueId).AddOrUpdateMonthPayment(monthCreate, realPaymentBudget, realPaymentOutBudget);
        //        }
        //    }

        //    {
        //        decimal dSumAvailBudget = 0, dSumAvailOutBudget = 0, dSumRealBudget = 0, dSumRealOutBudget = 0;
        //        Dictionary<int, decimal> dSumMonthBudget = new Dictionary<int, decimal>();
        //        Dictionary<int, decimal> dSumMonthOutBudget = new Dictionary<int, decimal>();

        //        foreach (var d in PlotedRecord)
        //        {
        //            dic.Add("A" + d.RowIndex, d.StName);
        //            dic.Add("B" + d.RowIndex, d.SbStName);
        //            dic.Add("C" + d.RowIndex, d.BgmProjCode);
        //            dic.Add("D" + d.RowIndex, d.BgmProjName);
        //            for (int i = 1; i <= 12; i++)
        //            {
        //                string strColMonthCrateBudget = BudgetMonthToColName(i, d.RowIndex);
        //                string strColMonthCrateOutBudget = OutBudgetMonthToColName(i, d.RowIndex);
        //                var strValueMonthCrateBudget = d.GetMonthBudgetPayment(i);
        //                var strValueMonthCrateOutBudget = d.GetMonthOutBudgetPayment(i);

        //                dic.Add(strColMonthCrateBudget, strValueMonthCrateBudget);
        //                dic.Add(strColMonthCrateOutBudget, strValueMonthCrateOutBudget);

        //                if (dSumMonthBudget.All(o => o.Key != i))
        //                {
        //                    dSumMonthBudget.Add(i, 0);
        //                    dSumMonthOutBudget.Add(i, 0);
        //                }
        //                dSumMonthBudget[i] += d.GetMonthBudgetPayment(i);
        //                dSumMonthOutBudget[i] += d.GetMonthOutBudgetPayment(i);
        //            }

        //            dSumRealBudget += d.GetAllMonthBudgetAmount();
        //            dSumRealOutBudget += d.GetAllMonthOutBudgetAmount();

        //            decimal dValueAllMonthCreateBudget = d.GetAllMonthBudgetAmount();
        //            decimal dValueAllMonthCreateOutBudget = d.GetAllMonthOutBudgetAmount();

        //            dic.Add("E" + d.RowIndex, d.AvailBudgetAmount);
        //            dic.Add("F" + d.RowIndex, d.AvailOutBudgetAmount);

        //            dSumAvailBudget += d.AvailBudgetAmount;
        //            dSumAvailOutBudget += d.AvailOutBudgetAmount;

        //            dic.Add("AE" + d.RowIndex, dValueAllMonthCreateBudget);
        //            dic.Add("AG" + d.RowIndex, dValueAllMonthCreateOutBudget);

        //            dic.Add("AF" + d.RowIndex, d.AvailBudgetAmount == 0 ? (object) 0.00 : ((dValueAllMonthCreateBudget/d.AvailBudgetAmount)*100));
        //            dic.Add("AH" + d.RowIndex, d.AvailOutBudgetAmount == 0 ? (object) 0.00 : ((dValueAllMonthCreateOutBudget/d.AvailOutBudgetAmount)*100));
        //        }

        //        dic.Add("E" + nRow, dSumAvailBudget);
        //        dic.Add("F" + nRow, dSumAvailOutBudget);

        //        for (int i = 1; i <= 12; i++)
        //        {
        //            string strColMonthCrateBudget = BudgetMonthToColName(i, nRow);
        //            string strColMonthCrateOutBudget = OutBudgetMonthToColName(i, nRow);
        //            dic.Add(strColMonthCrateBudget, dSumMonthBudget[i]);
        //            dic.Add(strColMonthCrateOutBudget, dSumMonthOutBudget[i]);
        //        }

        //        dic.Add("AE" + nRow, dSumRealBudget);
        //        dic.Add("AG" + nRow, dSumRealOutBudget);

        //        dic.Add("AF" + nRow, dSumAvailBudget == 0 ? (object) 0.00 : ((dSumRealBudget/dSumAvailBudget)*100));
        //        dic.Add("AH" + nRow, dSumAvailOutBudget == 0 ? (object) 0.00 : ((dSumRealOutBudget/dSumAvailOutBudget)*100));
        //    }

        //    RecordAmount = PlotedRecord.Count;

        //    List<int> columnToHide;
        //    switch (Report2Type)
        //    {
        //        case "Proj":
        //            columnToHide = new List<int>();
        //        dic.Add("D" + nRow, "รวม");
        //            break;
        //        case "SubStrat":
        //            columnToHide = new List<int>{3,4};
        //        dic.Add("B" + nRow, "รวม");
        //            break;
        //        case "Strat":
        //            columnToHide = new List<int>{2,3,4};
        //        dic.Add("A" + nRow, "รวม");
        //            break;
        //        default:
        //            throw new Exception("ประเภทรายงานไม่ถูกต้อง");
        //    }

        //    mailMerge.MergeExcel(Response
        //                         , Server.MapPath("~/Reports/Templates/แสดงรายจ่ายโครงการแยกตามแผนงาน.xlsx")
        //                         , string.Format("แสดงรายจ่ายโครงการแยกตามแผนงาน{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff"))
        //                         , dtbs
        //                         , null
        //                         , OfficeOpenXml.Table.TableStyles.None
        //                         , dic
        //                         , columnToHide);
        //}

        private class Record
        {
            public Record()
            {
                MonthPayment = new Dictionary<int, KeyValuePair<decimal, decimal>>();
            }

            public int UniqueId { get; set; }
            
            public int StSeq { get; set; }
            public string StName { get; set; }

            public int SbStSeq { get; set; }
            public string SbStName { get; set; }

            public int BgmProjSeq { get; set; }
            public string BgmProjCode { get; set; }
            public string BgmProjName { get; set; }

            public decimal AvailBudgetAmount { get; set; }
            public decimal AvailOutBudgetAmount { get; set; }

            public int RowIndex { get; set; }
            private Dictionary<int, KeyValuePair<decimal, decimal>> MonthPayment { get; set; }

            public void AddOrUpdateMonthPayment(int monthCreate, decimal paymentBudgetValue, decimal paymentOutBudgetValue)
            {
                var newValue = new KeyValuePair<decimal, decimal>(paymentBudgetValue, paymentOutBudgetValue);
                if (MonthPayment.All(o => o.Key != monthCreate))
                {
                    MonthPayment.Add(monthCreate, newValue);
                }
                else
                {
                    MonthPayment[monthCreate] = newValue;
                }
            }

            public decimal GetMonthBudgetPayment(int monthCreate)
            {
                if (MonthPayment.Any(o => o.Key == monthCreate))
                {
                    return MonthPayment[monthCreate].Key;
                }
                else
                {
                    return 0;
                }
            }

            public decimal GetMonthOutBudgetPayment(int monthCreate)
            {
                if (MonthPayment.Any(o => o.Key == monthCreate))
                {
                    return MonthPayment[monthCreate].Value;
                }
                else
                {
                    return 0;
                }
            }

            public decimal GetAllMonthBudgetAmount()
            {
                return MonthPayment.Sum(o => o.Value.Key);
            }

            public decimal GetAllMonthOutBudgetAmount()
            {
                return MonthPayment.Sum(o => o.Value.Value);
            }
        }

        private string BudgetMonthToColName(int monthCreate, int row)
        {
            string colMonthCrateBudget;
            switch (monthCreate)
            {
                case 10:
                    colMonthCrateBudget = "G" + row;
                    break;
                case 11:
                    colMonthCrateBudget = "I" + row;
                    break;
                case 12:
                    colMonthCrateBudget = "K" + row;
                    break;

                case 1:
                    colMonthCrateBudget = "M" + row;
                    break;
                case 2:
                    colMonthCrateBudget = "O" + row;
                    break;
                case 3:
                    colMonthCrateBudget = "Q" + row;
                    break;

                case 4:
                    colMonthCrateBudget = "S" + row;
                    break;
                case 5:
                    colMonthCrateBudget = "U" + row;
                    break;
                case 6:
                    colMonthCrateBudget = "W" + row;
                    break;

                case 7:
                    colMonthCrateBudget = "Y" + row;
                    break;
                case 8:
                    colMonthCrateBudget = "AA" + row;
                    break;
                case 9:
                    colMonthCrateBudget = "AC" + row;
                    break;
                default:
                    throw new Exception("None");
            }
            return colMonthCrateBudget;
        }

        private string OutBudgetMonthToColName(int monthCreate, int row)
        {
            string colMonthCrateOutBudget;
            switch (monthCreate)
            {
                case 10:
                    colMonthCrateOutBudget = "H" + row;
                    break;
                case 11:
                    colMonthCrateOutBudget = "J" + row;
                    break;
                case 12:
                    colMonthCrateOutBudget = "L" + row;
                    break;

                case 1:
                    colMonthCrateOutBudget = "N" + row;
                    break;
                case 2:
                    colMonthCrateOutBudget = "P" + row;
                    break;
                case 3:
                    colMonthCrateOutBudget = "R" + row;
                    break;

                case 4:
                    colMonthCrateOutBudget = "T" + row;
                    break;
                case 5:
                    colMonthCrateOutBudget = "V" + row;
                    break;
                case 6:
                    colMonthCrateOutBudget = "X" + row;
                    break;

                case 7:
                    colMonthCrateOutBudget = "Z" + row;
                    break;
                case 8:
                    colMonthCrateOutBudget = "AB" + row;
                    break;
                case 9:
                    colMonthCrateOutBudget = "AD" + row;
                    break;
                default:
                    throw new Exception("None");
            }
            return colMonthCrateOutBudget;
        }

        #endregion
    }
}